{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# 处理 Excel\r\n",
    "## 示例"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import openpyxl as xl"
   ],
   "outputs": [],
   "metadata": {
    "collapsed": true
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "# 加载工作簿\n",
    "wb = xl.load_workbook('transactions.xlsx')\n",
    "# 大小写敏感！\n",
    "sheet = wb['Sheet1']\n",
    "\n",
    "cell = sheet['a1']\n",
    "# cell = sheet.cell(1, 1)\n",
    "\n",
    "print(cell.value)\n",
    "\n",
    "print(sheet.max_row)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "transaction_id\n",
      "4\n"
     ]
    }
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "# 行号从 1 开始\r\n",
    "for row in range(2, sheet.max_row + 1):\r\n",
    "    cell = sheet.cell(row, 3)\r\n",
    "    print(cell)\r\n",
    "    print(cell.value)\r\n",
    "\r\n",
    "    # 改变数字\r\n",
    "    correct_price = cell.value * 0.9\r\n",
    "    # 规定位置\r\n",
    "    correct_price_cell = sheet.cell(row, 4)\r\n",
    "    # 改变对应位置的值\r\n",
    "    correct_price_cell.value = correct_price\r\n",
    "\r\n",
    "# 保存到新文件防止出错\r\n",
    "wb.save(\"transactions2.xlsx\")"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<Cell 'Sheet1'.C2>\n",
      "5.95\n",
      "<Cell 'Sheet1'.C3>\n",
      "6.95\n",
      "<Cell 'Sheet1'.C4>\n",
      "7.95\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "# 制图\r\n",
    "from openpyxl.chart import BarChart, Reference"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "values = Reference(sheet,\r\n",
    "                   min_row=2,\r\n",
    "                   max_row=sheet.max_row,\r\n",
    "                   min_col=4,\r\n",
    "                   max_col=4)\r\n",
    "\r\n",
    "chart = BarChart()\r\n",
    "chart.add_data(values)\r\n",
    "sheet.add_chart(chart, 'e2')\r\n",
    "\r\n",
    "wb.save('transactions2.xlsx')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 整理\r\n",
    "将有用的代码整理到 main.py 中，删掉不起作用的代码"
   ],
   "metadata": {}
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.7.4 64-bit ('base': conda)"
  },
  "language_info": {
   "name": "python",
   "version": "3.7.4",
   "mimetype": "text/x-python",
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "pygments_lexer": "ipython3",
   "nbconvert_exporter": "python",
   "file_extension": ".py"
  },
  "interpreter": {
   "hash": "a8f61be024eba58adef938c9aa1e29e02cb3dece83a5348b1a2dafd16a070453"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}